use anyhow::{Context, Result};
use chrono::{DateTime, Utc};
use rusqlite::{params, Connection};
use serde::{Deserialize, Serialize};
use std::collections::HashMap;

/// 数据库迁移记录
#[derive(Debug, Clone, Serialize, Deserialize)]
pub struct Migration {
    pub id: String,
    pub name: String,
    pub version: i32,
    pub up_sql: String,
    pub down_sql: String,
    pub checksum: String,
    pub applied_at: Option<DateTime<Utc>>,
}

/// 迁移管理器
pub struct MigrationManager {
    migrations: Vec<Migration>,
}

impl MigrationManager {
    /// 创建新的迁移管理器
    pub fn new() -> Self {
        let mut manager = Self {
            migrations: Vec::new(),
        };
        
        // 注册内置迁移
        manager.register_builtin_migrations();
        manager
    }

    /// 注册内置迁移
    fn register_builtin_migrations(&mut self) {
        // 初始化迁移 - 创建迁移记录表
        self.add_migration(Migration {
            id: "001_init_migrations".to_string(),
            name: "初始化迁移系统".to_string(),
            version: 1,
            up_sql: r#"
                CREATE TABLE IF NOT EXISTS migrations (
                    id TEXT PRIMARY KEY,
                    name TEXT NOT NULL,
                    version INTEGER NOT NULL,
                    checksum TEXT NOT NULL,
                    applied_at TEXT NOT NULL,
                    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
                );
                
                CREATE INDEX IF NOT EXISTS idx_migrations_version ON migrations(version);
                CREATE INDEX IF NOT EXISTS idx_migrations_applied_at ON migrations(applied_at);
            "#.to_string(),
            down_sql: "DROP TABLE IF EXISTS migrations;".to_string(),
            checksum: Self::calculate_checksum("001_init_migrations"),
            applied_at: None,
        });

        // 应用配置表迁移
        self.add_migration(Migration {
            id: "002_create_app_config".to_string(),
            name: "创建应用配置表".to_string(),
            version: 2,
            up_sql: r#"
                CREATE TABLE IF NOT EXISTS app_config (
                    id TEXT PRIMARY KEY,
                    theme TEXT NOT NULL DEFAULT 'system',
                    language TEXT NOT NULL DEFAULT 'zh-CN',
                    auto_start BOOLEAN NOT NULL DEFAULT FALSE,
                    minimize_to_tray BOOLEAN NOT NULL DEFAULT TRUE,
                    notifications BOOLEAN NOT NULL DEFAULT TRUE,
                    shortcuts TEXT NOT NULL DEFAULT '{}',
                    window_config TEXT NOT NULL DEFAULT '{}',
                    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
                );
                
                CREATE INDEX IF NOT EXISTS idx_app_config_updated_at ON app_config(updated_at);
                
                -- 插入默认配置
                INSERT OR IGNORE INTO app_config (
                    id, theme, language, shortcuts, window_config
                ) VALUES (
                    'default',
                    'system',
                    'zh-CN',
                    '{"toggleWindow":"CmdOrCtrl+Shift+H","newItem":"CmdOrCtrl+N","search":"CmdOrCtrl+F"}',
                    '{"width":1200,"height":800,"x":null,"y":null,"maximized":false}'
                );
            "#.to_string(),
            down_sql: "DROP TABLE IF EXISTS app_config;".to_string(),
            checksum: Self::calculate_checksum("002_create_app_config"),
            applied_at: None,
        });

        // 日志表迁移
        self.add_migration(Migration {
            id: "003_create_logs".to_string(),
            name: "创建日志表".to_string(),
            version: 3,
            up_sql: r#"
                CREATE TABLE IF NOT EXISTS logs (
                    id TEXT PRIMARY KEY,
                    level TEXT NOT NULL,
                    message TEXT NOT NULL,
                    module TEXT,
                    file TEXT,
                    line INTEGER,
                    target TEXT,
                    thread_id TEXT,
                    thread_name TEXT,
                    metadata TEXT,
                    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
                );
                
                CREATE INDEX IF NOT EXISTS idx_logs_level ON logs(level);
                CREATE INDEX IF NOT EXISTS idx_logs_created_at ON logs(created_at);
                CREATE INDEX IF NOT EXISTS idx_logs_module ON logs(module);
                CREATE INDEX IF NOT EXISTS idx_logs_level_created_at ON logs(level, created_at);
            "#.to_string(),
            down_sql: "DROP TABLE IF EXISTS logs;".to_string(),
            checksum: Self::calculate_checksum("003_create_logs"),
            applied_at: None,
        });

        // 用户数据表迁移
        self.add_migration(Migration {
            id: "004_create_user_data".to_string(),
            name: "创建用户数据表".to_string(),
            version: 4,
            up_sql: r#"
                CREATE TABLE IF NOT EXISTS user_data (
                    id TEXT PRIMARY KEY,
                    key TEXT NOT NULL UNIQUE,
                    value TEXT NOT NULL,
                    data_type TEXT NOT NULL DEFAULT 'string',
                    encrypted BOOLEAN NOT NULL DEFAULT FALSE,
                    expires_at TEXT,
                    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
                );
                
                CREATE INDEX IF NOT EXISTS idx_user_data_key ON user_data(key);
                CREATE INDEX IF NOT EXISTS idx_user_data_expires_at ON user_data(expires_at);
                CREATE INDEX IF NOT EXISTS idx_user_data_updated_at ON user_data(updated_at);
            "#.to_string(),
            down_sql: "DROP TABLE IF EXISTS user_data;".to_string(),
            checksum: Self::calculate_checksum("004_create_user_data"),
            applied_at: None,
        });

        // 插件表迁移
        self.add_migration(Migration {
            id: "005_create_plugins".to_string(),
            name: "创建插件表".to_string(),
            version: 5,
            up_sql: r#"
                CREATE TABLE IF NOT EXISTS plugins (
                    id TEXT PRIMARY KEY,
                    name TEXT NOT NULL,
                    version TEXT NOT NULL,
                    enabled BOOLEAN NOT NULL DEFAULT TRUE,
                    config TEXT NOT NULL DEFAULT '{}',
                    manifest TEXT NOT NULL,
                    install_path TEXT,
                    installed_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
                );
                
                CREATE INDEX IF NOT EXISTS idx_plugins_name ON plugins(name);
                CREATE INDEX IF NOT EXISTS idx_plugins_enabled ON plugins(enabled);
                CREATE INDEX IF NOT EXISTS idx_plugins_updated_at ON plugins(updated_at);
            "#.to_string(),
            down_sql: "DROP TABLE IF EXISTS plugins;".to_string(),
            checksum: Self::calculate_checksum("005_create_plugins"),
            applied_at: None,
        });
    }

    /// 添加迁移
    pub fn add_migration(&mut self, migration: Migration) {
        self.migrations.push(migration);
        // 按版本号排序
        self.migrations.sort_by_key(|m| m.version);
    }

    /// 执行所有待执行的迁移
    pub fn migrate(&self, conn: &Connection) -> Result<()> {
        log::info!("开始执行数据库迁移");

        // 首先确保迁移表存在
        let init_migration_applied = self.ensure_migration_table(conn)?;

        // 获取已应用的迁移
        let applied_migrations = self.get_applied_migrations(conn)?;

        // 执行未应用的迁移
        for migration in &self.migrations {
            // 如果是初始化迁移且已经在 ensure_migration_table 中应用过，跳过
            if migration.id == "001_init_migrations" && init_migration_applied {
                continue;
            }

            if !applied_migrations.contains_key(&migration.id) {
                self.apply_migration(conn, migration)?;
            }
        }

        log::info!("数据库迁移完成");
        Ok(())
    }

    /// 回滚到指定版本
    pub fn rollback_to_version(&self, conn: &Connection, target_version: i32) -> Result<()> {
        log::info!("开始回滚数据库到版本 {}", target_version);

        let applied_migrations = self.get_applied_migrations(conn)?;
        
        // 按版本倒序回滚
        let mut migrations_to_rollback: Vec<_> = self.migrations
            .iter()
            .filter(|m| m.version > target_version && applied_migrations.contains_key(&m.id))
            .collect();
        
        migrations_to_rollback.sort_by_key(|m| std::cmp::Reverse(m.version));

        for migration in migrations_to_rollback {
            self.rollback_migration(conn, migration)?;
        }

        log::info!("数据库回滚完成");
        Ok(())
    }

    /// 确保迁移表存在，返回是否应用了初始化迁移
    fn ensure_migration_table(&self, conn: &Connection) -> Result<bool> {
        // 检查迁移表是否已存在
        let table_exists: bool = conn.query_row(
            "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='migrations'",
            [],
            |row| {
                let count: i32 = row.get(0)?;
                Ok(count > 0)
            }
        ).unwrap_or(false);

        if table_exists {
            return Ok(false); // 表已存在，没有应用初始化迁移
        }

        // 如果是第一个迁移（创建迁移表），直接执行
        if let Some(first_migration) = self.migrations.first() {
            if first_migration.id == "001_init_migrations" {
                conn.execute(&first_migration.up_sql, [])
                    .context("创建迁移表失败")?;

                // 记录这个迁移已应用
                self.record_migration(conn, first_migration)?;
                return Ok(true); // 应用了初始化迁移
            }
        }
        Ok(false)
    }

    /// 获取已应用的迁移
    fn get_applied_migrations(&self, conn: &Connection) -> Result<HashMap<String, DateTime<Utc>>> {
        let mut stmt = conn.prepare(
            "SELECT id, applied_at FROM migrations ORDER BY version"
        )?;

        let migration_iter = stmt.query_map([], |row| {
            let id: String = row.get(0)?;
            let applied_at_str: String = row.get(1)?;
            let applied_at = applied_at_str.parse::<DateTime<Utc>>()
                .unwrap_or_else(|_| Utc::now());
            Ok((id, applied_at))
        })?;

        let mut applied = HashMap::new();
        for migration in migration_iter {
            let (id, applied_at) = migration?;
            applied.insert(id, applied_at);
        }

        Ok(applied)
    }

    /// 应用单个迁移
    fn apply_migration(&self, conn: &Connection, migration: &Migration) -> Result<()> {
        log::info!("应用迁移: {} - {}", migration.id, migration.name);

        // 开始事务
        let tx = conn.unchecked_transaction()?;

        // 执行迁移 SQL
        tx.execute_batch(&migration.up_sql)
            .with_context(|| format!("执行迁移 {} 失败", migration.id))?;

        // 记录迁移
        self.record_migration_in_tx(&tx, migration)?;

        // 提交事务
        tx.commit()
            .with_context(|| format!("提交迁移 {} 事务失败", migration.id))?;

        log::info!("迁移 {} 应用成功", migration.id);
        Ok(())
    }

    /// 回滚单个迁移
    fn rollback_migration(&self, conn: &Connection, migration: &Migration) -> Result<()> {
        log::info!("回滚迁移: {} - {}", migration.id, migration.name);

        // 开始事务
        let tx = conn.unchecked_transaction()?;

        // 执行回滚 SQL
        tx.execute_batch(&migration.down_sql)
            .with_context(|| format!("回滚迁移 {} 失败", migration.id))?;

        // 删除迁移记录
        tx.execute("DELETE FROM migrations WHERE id = ?", params![migration.id])
            .with_context(|| format!("删除迁移记录 {} 失败", migration.id))?;

        // 提交事务
        tx.commit()
            .with_context(|| format!("提交回滚 {} 事务失败", migration.id))?;

        log::info!("迁移 {} 回滚成功", migration.id);
        Ok(())
    }

    /// 记录迁移（在连接中）
    fn record_migration(&self, conn: &Connection, migration: &Migration) -> Result<()> {
        let now = Utc::now().to_rfc3339();
        conn.execute(
            "INSERT INTO migrations (id, name, version, checksum, applied_at) VALUES (?, ?, ?, ?, ?)",
            params![migration.id, migration.name, migration.version, migration.checksum, now],
        )?;
        Ok(())
    }

    /// 记录迁移（在事务中）
    fn record_migration_in_tx(&self, tx: &rusqlite::Transaction, migration: &Migration) -> Result<()> {
        let now = Utc::now().to_rfc3339();
        tx.execute(
            "INSERT INTO migrations (id, name, version, checksum, applied_at) VALUES (?, ?, ?, ?, ?)",
            params![migration.id, migration.name, migration.version, migration.checksum, now],
        )?;
        Ok(())
    }

    /// 计算校验和
    fn calculate_checksum(content: &str) -> String {
        use std::collections::hash_map::DefaultHasher;
        use std::hash::{Hash, Hasher};

        let mut hasher = DefaultHasher::new();
        content.hash(&mut hasher);
        format!("{:x}", hasher.finish())
    }

    /// 获取当前数据库版本
    pub fn get_current_version(&self, conn: &Connection) -> Result<i32> {
        let version: Result<i32, _> = conn.query_row(
            "SELECT MAX(version) FROM migrations",
            [],
            |row| row.get(0)
        );

        match version {
            Ok(v) => Ok(v),
            Err(_) => Ok(0), // 如果表不存在，返回版本 0
        }
    }

    /// 获取迁移状态
    pub fn get_migration_status(&self, conn: &Connection) -> Result<Vec<MigrationStatus>> {
        let applied_migrations = self.get_applied_migrations(conn)?;
        let mut status = Vec::new();

        for migration in &self.migrations {
            let applied_at = applied_migrations.get(&migration.id).cloned();
            status.push(MigrationStatus {
                id: migration.id.clone(),
                name: migration.name.clone(),
                version: migration.version,
                applied: applied_at.is_some(),
                applied_at,
            });
        }

        Ok(status)
    }
}

/// 迁移状态
#[derive(Debug, Clone, Serialize)]
pub struct MigrationStatus {
    pub id: String,
    pub name: String,
    pub version: i32,
    pub applied: bool,
    pub applied_at: Option<DateTime<Utc>>,
}
